﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Services;
using System.Data;
using pcsse.App_Code.entity;
using System.Data.SqlClient;
namespace pcsse.WebService
{
    /// <summary>
    /// Summary description for AppService
    /// </summary>
    [WebService(Namespace = "http://sse.tongji.edu.cn/spb/a")]
    [WebServiceBinding(ConformsTo = WsiProfiles.BasicProfile1_1)]
    [System.ComponentModel.ToolboxItem(false)]
    // To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line. 
    // [System.Web.Script.Services.ScriptService]
    public class AppService : System.Web.Services.WebService
    {


// Person
        [WebMethod]
        public DataTable GetPersons(string sid)
        {
            if (!CheckSession(sid))
                return null;

            DataTable dt = db_base.getDBhandle().GetDataTableBySQL("SELECT ID,NUMBER,NAME,GENDER,NATIONALITY,BIRTHDAY,IDNUMBER,TELEPNONE,EMAIL,PRIORITY_ID FROM T_PERSONINFO");
            dt.TableName = "PERSONINFO";
            return dt;
        }

        [WebMethod]
        public bool DeletePerson(string sid, string pid)
        {
            if (!CheckSession(sid))
                return false;

            SqlCommand cmd = new SqlCommand("DELETE FROM T_PERSONINFO WHERE ID=@ID");
            cmd.Parameters.Add("@ID", pid);
            db_base.getDBhandle().ExecuteCmd(cmd);

            return false;
        }

        [WebMethod]
        public bool AddPerson(string sid, person_info_r pi)
        {
            if (!CheckSession(sid))
                return false;

            SqlCommand cmd = new SqlCommand("INSERT INTO T_PERSONINFO (NUMBER, NAME, GENDER, NATIONALITY, BIRTHDAY, IDNUMBER, TELEPNONE, EMAIL, PASSWORD, PRIORITY_ID) VALUES (@NUMBER, @NAME, @GENDER, @NATIONALITY, @BIRTHDAY, @IDNUMBER, @TELEPHONE, @EMAIL, @PASSWORD, @PRIORITY_ID)");
            cmd.Parameters.Add("@NUMBER", pi.NUMBER);
            cmd.Parameters.Add("@NAME", pi.NAME);
            cmd.Parameters.Add("@GENDER", pi.GENDER);
            cmd.Parameters.Add("@NATIONALITY", pi.NATIONALITY);
            cmd.Parameters.Add("@BIRTHDAY", Convert.ToDateTime(pi.BIRTHDAY, null));
            cmd.Parameters.Add("@IDNUMBER", pi.IDNUMBER);
            cmd.Parameters.Add("@TELEPHONE", pi.TELEPHONE);
            cmd.Parameters.Add("@EMAIL", pi.EMAIL);
            cmd.Parameters.Add("@PASSWORD", pi.PASSWORD);
            cmd.Parameters.Add("@PRIORITY_ID", pi.PRIORITY_ID);
            db_base.getDBhandle().ExecuteCmd(cmd);

            return true;

        }

        [WebMethod]
        public bool UpdatePerson(string sid, person_info_r pi)
        {
            if (!CheckSession(sid))
                return false;

            SqlCommand cmd = new SqlCommand("UPDATE T_PERSONINFO SET NUMBER=@NUMBER, NAME=@NAME, GENDER=@GENDER, NATIONALITY=@NATIONALITY, BIRTHDAY=@BIRTHDAY, IDNUMBER=@IDNUMBER, TELEPNONE=@TELEPHONE, EMAIL=@EMAIL, PRIORITY_ID=@PRIORITY_ID WHERE ID=@ID");
            cmd.Parameters.Add("@ID", pi.ID);
            cmd.Parameters.Add("@NUMBER", pi.NUMBER);
            cmd.Parameters.Add("@NAME", pi.NAME);
            cmd.Parameters.Add("@GENDER", pi.GENDER);
            cmd.Parameters.Add("@NATIONALITY", pi.NATIONALITY);
            cmd.Parameters.Add("@BIRTHDAY", Convert.ToDateTime(pi.BIRTHDAY, null));
            cmd.Parameters.Add("@IDNUMBER", pi.IDNUMBER);
            cmd.Parameters.Add("@TELEPHONE", pi.TELEPHONE);
            cmd.Parameters.Add("@EMAIL", pi.EMAIL);
            cmd.Parameters.Add("@PRIORITY_ID", pi.PRIORITY_ID);

            try
            {
                db_base.getDBhandle().ExecuteCmd( cmd );
            }
            catch (System.Exception ex)
            {
            	throw ex;
            }


            return false;
        }


//Branch
        [WebMethod]
        public DataTable GetBranchs(string sid)
        {
            if(!CheckSession(sid))
            return null;
            DataTable dt = db_base.getDBhandle().GetDataTableBySQL("SELECT ID,YEAR,BR_NAME FROM T_BRANCH");
            dt.TableName = "BRANCH";
            return dt;
        }

        [WebMethod]
        public bool DeleteBranch(string sid, string bid)
        {
            if (!CheckSession(sid))
            return false;

            SqlCommand cmd = new SqlCommand("DELETE FROM T_BRANCH WHERE ID=@ID");
            cmd.Parameters.Add("@ID", bid);
            db_base.getDBhandle().ExecuteCmd(cmd);

            return true;
        }

        [WebMethod]
        public bool AddBranch(string sid, branch_info_r bi)
        {
            if (!CheckSession(sid))
                return false;

            SqlCommand cmd = new SqlCommand("INSERT INTO T_BRANCH (YEAR, BR_NAME) VALUES (@YEAR, @BR_NAME)");
            cmd.Parameters.Add("@YEAR", bi.YEAR);
            cmd.Parameters.Add("@BR_NAME", bi.BR_NAME);
            db_base.getDBhandle().ExecuteCmd(cmd);

            return true;

        }

        [WebMethod]
        public bool UpdateBranch(string sid, branch_info_r bi)
        {
            if (!CheckSession(sid))
            return false;

            SqlCommand cmd = new SqlCommand("UPDATE T_BRANCH SET YEAR=@YEAR, BR_NAME=@BR_NAME WHERE ID=@ID");
            cmd.Parameters.Add("@ID", bi.ID);
            cmd.Parameters.Add("@YEAR", bi.YEAR);
            cmd.Parameters.Add("@BR_NAME", bi.BR_NAME);
            db_base.getDBhandle().ExecuteCmd(cmd);



            return true;
        }

//Login

        private bool CheckSession(string sid)
        {
            DateTime dtime = new DateTime();
            SqlCommand cmd = new SqlCommand("SELECT LastTime FROM T_SESSION WHERE SID=@SID");
            cmd.Parameters.Add("@SID",sid);
            DataTable dt = db_base.getDBhandle().GetDataTableByCmd(cmd);
            if (dt.Rows.Count == 0)
                return false;
                //5分钟 过时
            else if ((DateTime.Now - Convert.ToDateTime(dt.Rows[0][0])).TotalMinutes > 4)
                return false;
            else
            {
                db_base.getDBhandle().ExecuteSql("UPDATE T_SESSION SET LastTIme='" + DateTime.Now + "' WHERE SID='" + sid + "'");
                return true;
            }
            
        }

        [WebMethod]
        public string Login(string uname, string password)
        {

            login lg = new login();
            int loginresult = lg.LoginByNumber(uname, password);
            if (loginresult <= 0)
            {
                return null;
            }
            else
            {
                person_info pi = new person_info();
                int pid = pi.GetPriorityByID(loginresult);
                if (pid < 3) return null;

                DataTable dt = db_base.getDBhandle().GetDataTableBySQL("SELECT * FROM T_SESSION WHERE UID='"+uname+"'");
                if (dt == null || dt.Rows.Count == 0)
                {
                    // new Session
                    db_base.getDBhandle().ExecuteSql("INSERT INTO T_SESSION(UID,LastTime) VALUES('"+uname+"','"+DateTime.Now+"')");

                }
                else
                {
                    //refresh Session
                    db_base.getDBhandle().ExecuteSql("UPDATE T_SESSION SET SID=newid() , LastTIme='"+DateTime.Now+"' WHERE UID='"+uname+"'");
                }

                return db_base.getDBhandle().GetDataTableBySQL("SELECT SID FROM T_SESSION WHERE UID='" + uname + "'").Rows[0][0].ToString();

            }

        }
        [WebMethod]
        public bool Logout(string sid)
        {
            SqlCommand cmd = new SqlCommand("DELETE FROM T_SESSION WHERE SID=@SID");
            cmd.Parameters.Add("@SID", sid);
            db_base.getDBhandle().ExecuteCmd(cmd);

            return true;
        }

        [WebMethod]
        public bool RenewSession(string sid)
        {
            return CheckSession(sid);
        }
    }
}
